﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Transactions;
using System.Configuration;

namespace DataControl
{
    /// <summary>
    /// SQL Server Functions
    /// </summary>
    public sealed class SQLServer
    {
        #region Private and internal Functions

        private const string sFieldPrefix = "db_";//Generic Type Field Prefix

        /// <summary>
        /// Convert SQL Server DATA_TYPE to C# SqlDbType
        /// </summary>
        /// <param name="TypeName">Server DATA_TYPE</param>
        /// <returns>SqlDbType</returns>
        internal static SqlDbType ConvertServerDbTypeToSqlDbType(string TypeName)
        {
            switch (TypeName.ToLower())
            {
                case "bigint":
                    return SqlDbType.BigInt;
                case "binary":
                    return SqlDbType.VarBinary;
                case "bit":
                    return SqlDbType.Bit;
                case "char":
                    return SqlDbType.Char;
                case "date":
                    return SqlDbType.Date;
                case "datetime":
                    return SqlDbType.DateTime;
                case "datetime2":
                    return SqlDbType.DateTime2;
                case "datetimeoffset":
                    return SqlDbType.DateTimeOffset;
                case "decimal":
                    return SqlDbType.Decimal;
                case "float":
                    return SqlDbType.Float;
                case "image":
                    return SqlDbType.Binary;
                case "int":
                    return SqlDbType.Int;
                case "money":
                    return SqlDbType.Money;
                case "nchar":
                    return SqlDbType.NChar;
                case "ntext":
                    return SqlDbType.NText;
                case "numeric":
                    return SqlDbType.Decimal;
                case "nvarchar":
                    return SqlDbType.NVarChar;
                case "real":
                    return SqlDbType.Real;
                case "rowversion":
                    return SqlDbType.Timestamp;
                case "smalldatetime":
                    return SqlDbType.DateTime;
                case "smallint":
                    return SqlDbType.SmallInt;
                case "smallmoney":
                    return SqlDbType.SmallMoney;
                case "sql_variant":
                    return SqlDbType.Variant;
                case "text":
                    return SqlDbType.Text;
                case "time":
                    return SqlDbType.Time;
                case "timestamp":
                    return SqlDbType.Timestamp;
                case "tinyint":
                    return SqlDbType.TinyInt;
                case "uniqueidentifier":
                    return SqlDbType.UniqueIdentifier;
                case "varbinary":
                    return SqlDbType.VarBinary;
                case "varchar":
                    return SqlDbType.VarChar;
                case "xml":
                    return SqlDbType.Xml;
                default:
                    throw new Exception("Invalid Data Type");
            }
        }

        /// <summary>
        /// Gets correct size for SqlParameter.Size
        /// </summary>
        /// <param name="TypeName">Server DATA_TYPE</param>
        /// <param name="TypeSize">Server CHARACTER_MAXIMUM_LENGTH</param>
        /// <returns>Valid SqlParameter.Size</returns>
        private static int EvaluateParamSize(string TypeName, int TypeSize)
        {
            switch (TypeName.ToLower())
            {
                case "geography":
                case "geometry":
                case "hierarchyid":
                case "image":
                case "ntext":
                case "sql_variant":
                case "text":
                case "xml":
                    return 0;

                case "nvarchar":
                case "varchar":
                case "varbinary":
                    //if (TypeSize < 0) return 0; else return TypeSize;
                    return TypeSize;

                case "char":
                case "nchar":
                case "binary":
                    return TypeSize;

                case "bit":
                case "tinyint":
                    return 1;

                case "smallint":
                    return 2;

                case "date":
                    return 3;

                case "int":
                case "real":
                case "smalldatetime":
                case "smallmoney":
                    return 4;

                case "time":
                    return 5;

                case "bigint":
                case "datetime":
                case "datetime2":
                case "float":
                case "money":
                case "timestamp":
                    return 8;

                case "decimal":
                case "numeric":
                    return 9;

                case "datetimeoffset":
                    return 10;

                case "uniqueidentifier":
                    return 16;

                default:
                    throw new Exception("Invalid Data Type");
            }
        }

        /// <summary>
        /// Append SqlCommand output parameters to Parameter Dictionary
        /// </summary>
        /// <param name="sqlCommand">Output SqlCommand</param>
        /// <param name="Parameters">Stored Procedure Parameters Dictionary</param>
        private static void AppendReturnValues(ref SqlCommand sqlCommand, ref Dictionary<string, object> Parameters)
        {
            if (Parameters == null) return;

            int iParamCount = 0;
            while (iParamCount < sqlCommand.Parameters.Count)
            {
                if (sqlCommand.Parameters[iParamCount].Direction == ParameterDirection.InputOutput || sqlCommand.Parameters[iParamCount].Direction == ParameterDirection.Output)
                {
                    if (Parameters.ContainsKey(sqlCommand.Parameters[iParamCount].ParameterName))
                        Parameters[sqlCommand.Parameters[iParamCount].ParameterName] = sqlCommand.Parameters[iParamCount].Value;
                    else
                        Parameters.Add(sqlCommand.Parameters[iParamCount].ParameterName, sqlCommand.Parameters[iParamCount].Value);
                }
                iParamCount++;
            }
        }

        /// <summary>
        /// Create Executin Stored Procedure Command. This Function Opens the SQL Connection
        /// </summary>
        /// <param name="SPSchema">Stored Procedure Schema Name</param>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="sqlConnectionTransaction">Sql Connection for transaction execution</param>
        /// <param name="Parameters">Stored Procedure Parameters Dictionary</param>
        /// <param name="sqlConnectionQuery">Sql Connection for parameter Query</param>
        /// <param name="CloseQueryConnection">Close Sql Connection for parameter Query</param>
        /// <returns>Executable Stored Procedure Command</returns>
        private static SqlCommand CreateExecuteCommandFromSQLQuery(string SPSchema, string SPName, SqlConnection sqlConnectionTransaction, ref Dictionary<string, object> Parameters, SqlConnection sqlConnectionQuery, bool CloseQueryConnection)
        {
            SqlCommand sqlExeCommand = null;

            try
            {
                sqlExeCommand = new SqlCommand("[" + SPSchema + "].[" + SPName + "]", sqlConnectionTransaction);
                sqlExeCommand.CommandType = CommandType.StoredProcedure;
                sqlExeCommand.Parameters.Add(new SqlParameter("@Return", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, "Return", DataRowVersion.Current, null));

                DataTable dtSPParams = GetStoredProcedureParams(SPSchema, SPName, sqlConnectionQuery, CloseQueryConnection);

                //If No Parameters found
                if (dtSPParams.Rows.Count == 0) return sqlExeCommand;

                int iParamCount = 0;

                while (iParamCount < dtSPParams.Rows.Count)
                {
                    SqlParameter sqlExeParam = new SqlParameter(dtSPParams.Rows[iParamCount][0].ToString(),
                                                                ConvertServerDbTypeToSqlDbType(dtSPParams.Rows[iParamCount][1].ToString()),
                                                                EvaluateParamSize(dtSPParams.Rows[iParamCount][1].ToString(), (int)dtSPParams.Rows[iParamCount][2]),
                                                                ParameterDirection.Input,
                                                                true,
                                                                byte.Parse(dtSPParams.Rows[iParamCount][3].ToString()),
                                                                byte.Parse(dtSPParams.Rows[iParamCount][4].ToString()),
                                                                dtSPParams.Rows[iParamCount][0].ToString().Remove(0),
                                                                DataRowVersion.Current,
                                                                null);

                    if (dtSPParams.Rows[iParamCount][5].ToString() != "IN") { sqlExeParam.Direction = ParameterDirection.InputOutput; sqlExeParam.Value = DBNull.Value; }

                    if (Parameters != null)
                        if (Parameters.ContainsKey(dtSPParams.Rows[iParamCount][0].ToString()))
                        {
                            sqlExeParam.IsNullable = false;
                            sqlExeParam.Value = Parameters[dtSPParams.Rows[iParamCount][0].ToString()];
                        }

                    sqlExeCommand.Parameters.Add(sqlExeParam);

                    iParamCount++;
                }

                ////Strict Paramater Check
                if (Parameters != null)
                    foreach (KeyValuePair<string, object> keyValuePair in Parameters)
                        if (!sqlExeCommand.Parameters.Contains(keyValuePair.Key))
                            throw new Exception(keyValuePair.Key + " is not a valid parameter of " + SPName);
                ////Strict Paramater Check
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            if (sqlExeCommand != null)
                sqlExeCommand.CommandTimeout = 300;
            return sqlExeCommand;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="SPSchema">Stored Procedure Schema Name</param>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="sqlConnection">Sql Connection for parameter Query</param>
        /// <param name="CloseQueryConnection">Close connection after query</param>
        /// <returns>Datatable containing stored procedure paramaters</returns>
        private static DataTable GetStoredProcedureParams(string SPSchema, string SPName, SqlConnection sqlConnection, bool CloseQueryConnection)
        {
            SqlCommand sqlCommand = null;
            SqlDataReader sqlParamReader = null;

            DataTable dataTable = null;
            DataRow dataRow = null;

            try
            {
                //Create dtSPParams Columns
                dataTable = new DataTable("SQLParams");
                dataTable.Columns.Add("PARAMETER_NAME", Type.GetType("System.String"));
                dataTable.Columns.Add("DATA_TYPE", Type.GetType("System.String"));
                dataTable.Columns.Add("CHARACTER_MAXIMUM_LENGTH", Type.GetType("System.Int32"));
                dataTable.Columns.Add("NUMERIC_PRECISION", Type.GetType("System.Int32"));
                dataTable.Columns.Add("NUMERIC_SCALE", Type.GetType("System.Int32"));
                dataTable.Columns.Add("PARAMETER_MODE", Type.GetType("System.String"));

                //sqlCommand = new SqlCommand("[Foundation].[upU_Get_SPParams]", sqlConnection);
                string strSPParam = "SELECT PARAMETER_NAME, DATA_TYPE, ISNULL(CHARACTER_MAXIMUM_LENGTH, 0) AS [CHARACTER_MAXIMUM_LENGTH], ISNULL(CAST(NUMERIC_PRECISION AS INT), 0) AS [NUMERIC_PRECISION], ISNULL(NUMERIC_SCALE, 0) AS [NUMERIC_SCALE], PARAMETER_MODE FROM INFORMATION_SCHEMA.PARAMETERS (NOLOCK) WHERE SPECIFIC_SCHEMA = @SPSchema AND SPECIFIC_NAME = @SPName ORDER BY ORDINAL_POSITION ASC";
                sqlCommand = new SqlCommand(strSPParam, sqlConnection);
                //sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandType = CommandType.Text;

                sqlCommand.Parameters.Add(new SqlParameter("@Return", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, "Return", DataRowVersion.Current, null));
                sqlCommand.Parameters.Add(new SqlParameter("@SPSchema", SqlDbType.NVarChar, 128, ParameterDirection.Input, false, 0, 0, "SPSchema", DataRowVersion.Current, SPSchema));
                sqlCommand.Parameters.Add(new SqlParameter("@SPName", SqlDbType.NVarChar, 128, ParameterDirection.Input, false, 0, 0, "SPName", DataRowVersion.Current, SPName));

                OpenSQLConnectionSupressTrx(sqlConnection);

                sqlParamReader = sqlCommand.ExecuteReader(CommandBehavior.SequentialAccess);

                dataTable.BeginLoadData();

                while (sqlParamReader.Read())
                {
                    dataRow = dataTable.NewRow();

                    dataRow[0] = sqlParamReader.GetString(0);
                    dataRow[1] = sqlParamReader.GetString(1);
                    dataRow[2] = sqlParamReader.GetInt32(2);
                    dataRow[3] = sqlParamReader.GetInt32(3);
                    dataRow[4] = sqlParamReader.GetInt32(4);
                    dataRow[5] = sqlParamReader.GetString(5);

                    dataTable.Rows.Add(dataRow);
                }

                CloseSqlReader(ref sqlParamReader);

                dataTable.AcceptChanges();
                dataTable.BeginLoadData();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }

            finally
            {
                CloseSqlReader(ref sqlParamReader);

                if (sqlCommand != null)
                {
                    sqlCommand.Dispose();
                    sqlCommand = null;
                }
                if (CloseQueryConnection)
                    CloseSQLConnection(sqlConnection, false);
            }

            return dataTable;
        }

        /// <summary>
        /// Set variable data using variable name
        /// </summary>
        /// <param name="TargetObject">Target object</param>
        /// <param name="VariableName">Target variable name</param>
        /// <param name="Data">Data to be assigned</param>
        private static void SetVariableByName(object TargetObject, string VariableName, object Data)
        {
            System.Reflection.FieldInfo fieldInfo = TargetObject.GetType().GetField(VariableName, System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance);
            if (fieldInfo == null)
                return;

            if (Data is DBNull) return;

            fieldInfo.SetValue(TargetObject, Data);
        }

        #endregion Private and internal Functions

        #region Public Static Functions

        /// <summary>
        /// Read Connection String from Config File.
        /// </summary>
        /// <returns></returns>
        //public static SqlConnection CreateConnectionQuery()
        //{
        //    SqlConnection sqlConn = null;
        //    try
        //    {
        //        sqlConn = new SqlConnection(ConfigurationManager.AppSettings["DEVDBNONTRXSTRING"]);
        //    }
        //    catch (Exception ex)
        //    {
        //        //WebUtil.FunctionException.Exception("eComm.B2C.DataAccessLayer.SQLServer", "CreateConnectionQuery", ex.Message);
        //    }
        //    return sqlConn;
        //}


        /// <summary>
        /// Open SqlConnection in Ambient Transaction. New Transaction is created if Ambient is not found.
        /// </summary>
        /// <param name="sqlConnection">SqlConnection to be opened</param>
        public static void OpenSQLConnection(SqlConnection sqlConnection)
        {
            if (sqlConnection.State == ConnectionState.Open) return;//Exit if already open;

            System.Transactions.Transaction AmbientTransaction = null;

            if (System.Transactions.Transaction.Current != null)
            {
                AmbientTransaction = System.Transactions.Transaction.Current;
                System.Transactions.Transaction.Current = null;
            }

            try
            {
                if (sqlConnection.State != ConnectionState.Open)
                {
                    if (sqlConnection.State != ConnectionState.Closed)
                        sqlConnection.Close();

                    sqlConnection.Open();

                    if (AmbientTransaction != null)
                        sqlConnection.EnlistTransaction(AmbientTransaction);

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (AmbientTransaction != null)
                    System.Transactions.Transaction.Current = AmbientTransaction;
            }
        }

        /// <summary>
        /// Open SqlConnection and suppress current transaction.
        /// </summary>
        /// <param name="sqlConnection">SqlConnection to be opened</param>
        public static void OpenSQLConnectionSupressTrx(SqlConnection sqlConnection)
        {
            System.Transactions.Transaction AmbientTransaction = null;

            if (System.Transactions.Transaction.Current != null)
            {
                AmbientTransaction = System.Transactions.Transaction.Current;
                System.Transactions.Transaction.Current = null;
            }

            try
            {
                if (sqlConnection.State != ConnectionState.Open)
                {
                    if (sqlConnection.State != ConnectionState.Closed)
                        sqlConnection.Close();

                    sqlConnection.Open();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (AmbientTransaction != null)
                    System.Transactions.Transaction.Current = AmbientTransaction;
            }
        }

        /// <summary>
        /// Close SqlConnection
        /// </summary>
        /// <param name="sqlConnection">SqlConnection to be closed</param>
        public static void CloseSQLConnection(SqlConnection sqlConnection, bool NullConnection)
        {
            try
            {
                if (sqlConnection != null)
                {
                    if (sqlConnection.State != ConnectionState.Closed)
                        sqlConnection.Close();

                    if (NullConnection)
                    {
                        sqlConnection.Dispose();
                        sqlConnection = null;
                    }
                }
            }
            catch { }
        }

        /// <summary>
        /// Close SqlReader
        /// </summary>
        /// <param name="sqlDataReader">SqlDataReader to be closed</param>
        public static void CloseSqlReader(ref SqlDataReader sqlDataReader)
        {
            if (sqlDataReader != null)
            {
                if (!sqlDataReader.IsClosed)
                    sqlDataReader.Close();

                sqlDataReader.Dispose();
                sqlDataReader = null;
            }
        }

        /// <summary>
        /// Read Data into DataTable
        /// </summary>
        /// <param name="sqlDataReader">SqlReader</param>
        /// <param name="dataTable">DataTable</param>
        private static void ReadDataTable_OLD(ref SqlDataReader sqlDataReader, ref DataTable dataTable)
        {
            if (sqlDataReader.FieldCount <= 0) return;

            dataTable.BeginLoadData();

            //Create Table Columns
            int iFieldCounter = 0;
            while (iFieldCounter < sqlDataReader.FieldCount)
            {
                dataTable.Columns.Add(sqlDataReader.GetName(iFieldCounter).Trim(), sqlDataReader.GetFieldType(iFieldCounter));
                iFieldCounter++;
            }

            //Load Data
            while (sqlDataReader.Read())
            {
                object[] objDataRow = new object[sqlDataReader.FieldCount];

                iFieldCounter = 0;
                while (iFieldCounter < sqlDataReader.FieldCount)
                {
                    objDataRow[iFieldCounter] = new object();
                    objDataRow[iFieldCounter] = sqlDataReader.GetValue(iFieldCounter);
                    iFieldCounter++;
                }
                dataTable.LoadDataRow(objDataRow, false);
            }
            dataTable.AcceptChanges();

            dataTable.EndLoadData();
        }
        private static void ReadDataTable(ref SqlDataReader sqlDataReader, ref DataTable dataTable)
        {
            if (sqlDataReader.FieldCount <= 0) return;

            dataTable.BeginLoadData();

            //Create Table Columns
            List<int> lstColumn = new List<int>();
            int iFieldCounter = 0;
            while (iFieldCounter < sqlDataReader.FieldCount)
            {
                if (!dataTable.Columns.Contains(sqlDataReader.GetName(iFieldCounter).Trim()))
                {
                    dataTable.Columns.Add(sqlDataReader.GetName(iFieldCounter).Trim(), sqlDataReader.GetFieldType(iFieldCounter));
                    lstColumn.Add(iFieldCounter);
                }
                iFieldCounter++;
            }

            //Load Data
            while (sqlDataReader.Read())
            {
                object[] objDataRow = new object[lstColumn.Count];

                iFieldCounter = 0;
                while (iFieldCounter < lstColumn.Count)
                {
                    objDataRow[iFieldCounter] = new object();
                    objDataRow[iFieldCounter] = sqlDataReader.GetValue(lstColumn[iFieldCounter]);
                    iFieldCounter++;
                }
                dataTable.LoadDataRow(objDataRow, false);
            }
            dataTable.AcceptChanges();

            dataTable.EndLoadData();
        }

        /// <summary>
        /// Read Data into generic list
        /// </summary>
        /// <typeparam name="T">Type in List</typeparam>
        /// <param name="sqlDataReader">SqlReader</param>
        /// <param name="genericList">List of generic data</param>
        private static void ReadDataTable<T>(ref SqlDataReader sqlDataReader, ref List<T> genericList)
            where T : new()
        {

            try
            {
                if (sqlDataReader.FieldCount <= 0) return;

                //Load Data
                while (sqlDataReader.Read())
                {
                    T genericType = new T();

                    int iFieldCounter = 0;
                    while (iFieldCounter < sqlDataReader.FieldCount)
                    {
                        SetVariableByName(genericType, sFieldPrefix + sqlDataReader.GetName(iFieldCounter).Trim(), sqlDataReader.GetValue(iFieldCounter));
                        iFieldCounter++;
                    }

                    genericList.Add(genericType);
                }
            }
            catch (Exception ex)
            {
                ex.ToString();
            }
        }

        #region ExecuteNonQueryProcedure - FromSQLQuery

        /// <summary>
        /// Execute Non Query Stored Procedure
        /// </summary>
        /// <param name="SPSchema">Stored Procedure Schema Name</param>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="Parameters">Stored Procedure Parameters Dictionary</param>
        /// <param name="sqlConnectionTransaction">Sql Connection for transaction and Execution</param>
        /// <param name="sqlConnectionQuery">Sql Connection for parameter query</param>
        /// <param name="CloseTransactionConnection">Auto close transaction connection after execution</param>
        /// <param name="CloseQueryConnection">Auto close parameter query connection after execution</param>
        /// <returns>Stored Procedure return value</returns>
        public static int ExecuteNonQueryProcedure(string SPSchema, string SPName, ref Dictionary<string, object> Parameters, SqlConnection sqlConnectionTransaction, SqlConnection sqlConnectionQuery, bool CloseTransactionConnection, bool CloseQueryConnection)
        {
            int iReturnValue = -100000;//Default to Unclassified error

            SqlCommand sqlCommand = null;

            try
            {
                sqlCommand = CreateExecuteCommandFromSQLQuery(SPSchema, SPName, sqlConnectionTransaction, ref Parameters, sqlConnectionQuery, CloseQueryConnection);

                OpenSQLConnection(sqlConnectionTransaction);

                sqlCommand.ExecuteNonQuery();

                //All Stored Procedures Must return Value Do not comment this
                if (sqlCommand.Parameters[0].Value == null)
                    throw new Exception("Stored procedure return value cannot be null");

                iReturnValue = (int)sqlCommand.Parameters[0].Value;

                AppendReturnValues(ref sqlCommand, ref Parameters);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlCommand != null)
                {
                    sqlCommand.Dispose();
                    sqlCommand = null;
                }

                if (CloseTransactionConnection)
                    CloseSQLConnection(sqlConnectionTransaction, false);
            }
            return iReturnValue;
        }

        /// <summary>
        /// Execute Non Query Stored Procedure
        /// </summary>
        /// <param name="SPSchema">Stored Procedure Schema Name</param>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="Parameters">Stored Procedure Parameters Dictionary</param>
        /// <param name="sqlConnectionTransaction">Sql Connection for transaction and Execution</param>
        /// <param name="sqlConnectionQuery">Sql Connection for parameter query</param>
        /// <param name="CloseTransactionConnection">Auto close transaction connection after execution</param>
        /// <param name="CloseQueryConnection">Auto close parameter query connection after execution</param>
        /// <returns>Stored Procedure return value</returns>
        public static int ExecuteNonQueryProcedure(string SPSchema, string SPName, ref Dictionary<string, object> Parameters, SqlConnection sqlConnectionTransaction, SqlConnection sqlConnectionQuery, bool CloseTransactionConnection)
        {
            return ExecuteNonQueryProcedure(SPSchema, SPName, ref Parameters, sqlConnectionTransaction, sqlConnectionQuery, CloseTransactionConnection, true);
        }

        /// <summary>
        /// Execute Non Query Stored Procedure
        /// </summary>
        /// <param name="SPSchema">Stored Procedure Schema Name</param>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="Parameters">Stored Procedure Parameters Dictionary</param>
        /// <param name="sqlConnectionTransaction">Sql Connection for transaction and Execution</param>
        /// <param name="sqlConnectionQuery">Sql Connection for parameter query</param>
        /// <param name="CloseTransactionConnection">Auto close transaction connection after execution</param>
        /// <param name="CloseQueryConnection">Auto close parameter query connection after execution</param>
        /// <returns>Stored Procedure return value</returns>
        public static int ExecuteNonQueryProcedure(string SPSchema, string SPName, ref Dictionary<string, object> Parameters, SqlConnection sqlConnectionTransaction, SqlConnection sqlConnectionQuery)
        {
            return ExecuteNonQueryProcedure(SPSchema, SPName, ref Parameters, sqlConnectionTransaction, sqlConnectionQuery, true, true);
        }

        #endregion ExecuteNonQueryProcedure - FromSQLQuery

        #region ExecuteDataTableQueryProcedure - FromSQLQuery

        /// <summary>
        /// Execute Query Stored Procedure and returns results in DataTable
        /// </summary>
        /// <param name="SPSchema">Stored Procedure Schema Name</param>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="Parameters">Stored Procedure Parameters Dictionary</param>
        /// <param name="sqlConnection">Sql Connection for transaction and execution</param>
        /// <param name="sqlConnectionQuery">Sql Connection for paramter query</param>
        /// <param name="dataTable">Result DataTable</param>
        /// <param name="CloseTransactionConnection">close transaction connection after execution</param>
        /// <param name="CloseQueryConnection">close parameter query connection after execution</param>
        /// <returns>Stored Procedure return value</returns>
        public static int ExecuteDataTableQueryProcedure(string SPSchema, string SPName, ref Dictionary<string, object> Parameters, SqlConnection sqlConnectionTransaction, SqlConnection sqlConnectionQuery, ref DataTable dataTable, bool CloseTransactionConnection, bool CloseQueryConnection)
        {
            int iReturnValue = -100000;//Default to Unclassified error

            SqlCommand sqlCommand = null;
            SqlDataReader sqlDataReader = null;

            try
            {
                sqlCommand = CreateExecuteCommandFromSQLQuery(SPSchema, SPName, sqlConnectionTransaction, ref Parameters, sqlConnectionQuery, CloseQueryConnection);

                OpenSQLConnectionSupressTrx(sqlConnectionTransaction);

                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.SequentialAccess);

                do
                {
                    if (dataTable != null) dataTable.Dispose();

                    dataTable = new DataTable();
                    ReadDataTable(ref sqlDataReader, ref dataTable);
                } while (sqlDataReader.NextResult());

                //All Stored Procedures Must return Value Do not comment this
                if (sqlCommand.Parameters[0].Value == null)
                    throw new Exception("Stored procedure return value cannot be null");

                iReturnValue = (int)sqlCommand.Parameters[0].Value;

                AppendReturnValues(ref sqlCommand, ref Parameters);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseSqlReader(ref sqlDataReader);

                if (sqlCommand != null)
                {
                    sqlCommand.Dispose();
                    sqlCommand = null;
                }

                if (CloseTransactionConnection)
                    CloseSQLConnection(sqlConnectionTransaction, false);
            }
            return iReturnValue;
        }

        /// <summary>
        /// Execute Query Stored Procedure and returns results in DataTable
        /// </summary>
        /// <param name="SPSchema">Stored Procedure Schema Name</param>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="Parameters">Stored Procedure Parameters Dictionary</param>
        /// <param name="sqlConnection">Sql Connection for transaction and execution</param>
        /// <param name="sqlConnectionQuery">Sql Connection for paramter query</param>
        /// <param name="dataTable">Result DataTable</param>
        /// <param name="CloseTransactionConnection">close transaction connection after execution</param>
        /// <returns>Stored Procedure return value</returns>
        public static int ExecuteDataTableQueryProcedure(string SPSchema, string SPName, ref Dictionary<string, object> Parameters, SqlConnection sqlConnectionTransaction, SqlConnection sqlConnectionQuery, ref DataTable dataTable, bool CloseTransactionConnection)
        {
            return ExecuteDataTableQueryProcedure(SPSchema, SPName, ref Parameters, sqlConnectionTransaction, sqlConnectionQuery, ref dataTable, CloseTransactionConnection, true);
        }

        /// <summary>
        /// Execute Query Stored Procedure and returns results in DataTable
        /// </summary>
        /// <param name="SPSchema">Stored Procedure Schema Name</param>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="Parameters">Stored Procedure Parameters Dictionary</param>
        /// <param name="sqlConnection">Sql Connection for transaction and execution</param>
        /// <param name="sqlConnectionQuery">Sql Connection for paramter query</param>
        /// <param name="dataTable">Result DataTable</param>
        /// <returns>Stored Procedure return value</returns>
        public static int ExecuteDataTableQueryProcedure(string SPSchema, string SPName, ref Dictionary<string, object> Parameters, SqlConnection sqlConnectionTransaction, SqlConnection sqlConnectionQuery, ref DataTable dataTable)
        {
            return ExecuteDataTableQueryProcedure(SPSchema, SPName, ref Parameters, sqlConnectionTransaction, sqlConnectionQuery, ref dataTable, true, true);
        }

        #endregion ExecuteDataTableQueryProcedure - FromSQLQuery

        #region ExecuteDataSetQueryProcedure - FromSQLQuery

        /// <summary>
        /// Execute Query Multiple result Stored Procedure and returns results in Dataset
        /// </summary>
        /// <param name="SPSchema">Stored Procedure Schema Name</param>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="Parameters">Stored Procedure Parameters Dictionary</param>
        /// <param name="sqlConnectionTransaction">Sql Connection for transaction and execution</param>
        /// <param name="sqlConnectionQuery">Sql Connection for parameter query</param>
        /// <param name="dataSet">Result DataSet</param>
        /// <param name="CloseTransactionConnection">Close transaction connection after execution</param>
        /// <param name="CloseQueryConnection">Close parameter query connection after execution</param>
        /// <returns>Stored Procedure return value</returns>
        public static int ExecuteDataSetQueryProcedure(string SPSchema, string SPName, ref Dictionary<string, object> Parameters, SqlConnection sqlConnectionTransaction, SqlConnection sqlConnectionQuery, ref DataSet dataSet, bool CloseTransactionConnection, bool CloseQueryConnection)
        {
            int iReturnValue = -100000;//Default to Unclassified error

            SqlCommand sqlCommand = null;
            SqlDataReader sqlDataReader = null;

            try
            {
                dataSet = new DataSet();

                sqlCommand = CreateExecuteCommandFromSQLQuery(SPSchema, SPName, sqlConnectionTransaction, ref Parameters, sqlConnectionQuery, CloseQueryConnection);

                OpenSQLConnectionSupressTrx(sqlConnectionTransaction);

                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.SequentialAccess);

                do
                {
                    DataTable dataTable = new DataTable();

                    ReadDataTable(ref sqlDataReader, ref dataTable);

                    dataSet.Tables.Add(dataTable);

                } while (sqlDataReader.NextResult());

                //All Stored Procedures Must return Value Do not comment this
                if (sqlCommand.Parameters[0].Value == null)
                    throw new Exception("Stored procedure return value cannot be null");

                iReturnValue = (int)sqlCommand.Parameters[0].Value;

                AppendReturnValues(ref sqlCommand, ref Parameters);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseSqlReader(ref sqlDataReader);

                if (sqlCommand != null)
                {
                    sqlCommand.Dispose();
                    sqlCommand = null;
                }

                if (CloseTransactionConnection)
                    CloseSQLConnection(sqlConnectionTransaction, false);
            }
            return iReturnValue;
        }

        /// <summary>
        /// Execute Query Multiple result Stored Procedure and returns results in Dataset
        /// </summary>
        /// <param name="SPSchema">Stored Procedure Schema Name</param>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="Parameters">Stored Procedure Parameters Dictionary</param>
        /// <param name="sqlConnectionTransaction">Sql Connection for transaction and execution</param>
        /// <param name="sqlConnectionQuery">Sql Connection for parameter query</param>
        /// <param name="dataSet">Result DataSet</param>
        /// <param name="CloseTransactionConnection">Close transaction connection after execution</param>
        /// <returns>Stored Procedure return value</returns>
        public static int ExecuteDataSetQueryProcedure(string SPSchema, string SPName, ref Dictionary<string, object> Parameters, SqlConnection sqlConnectionTransaction, SqlConnection sqlConnectionQuery, ref DataSet dataSet, bool CloseTransactionConnection)
        {
            return ExecuteDataSetQueryProcedure(SPSchema, SPName, ref Parameters, sqlConnectionTransaction, sqlConnectionQuery, ref dataSet, CloseTransactionConnection, true);
        }

        /// <summary>
        /// Execute Query Multiple result Stored Procedure and returns results in Dataset
        /// </summary>
        /// <param name="SPSchema">Stored Procedure Schema Name</param>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="Parameters">Stored Procedure Parameters Dictionary</param>
        /// <param name="sqlConnectionTransaction">Sql Connection for transaction and execution</param>
        /// <param name="sqlConnectionQuery">Sql Connection for parameter query</param>
        /// <param name="dataSet">Result DataSet</param>
        /// <returns>Stored Procedure return value</returns>
        public static int ExecuteDataSetQueryProcedure(string SPSchema, string SPName, ref Dictionary<string, object> Parameters, SqlConnection sqlConnectionTransaction, SqlConnection sqlConnectionQuery, ref DataSet dataSet)
        {
            return ExecuteDataSetQueryProcedure(SPSchema, SPName, ref Parameters, sqlConnectionTransaction, sqlConnectionQuery, ref dataSet, true, true);
        }

        #endregion ExecuteDataSetQueryProcedure - FromSQLQuery

        #region ExecuteGenericListQueryProcedure - FromSQLQuery

        /// <summary>
        /// Execute Query Stored Procedure and returns results generic list
        /// </summary>
        /// <typeparam name="T">Generic data type</typeparam>
        /// <param name="SPSchema">Stored Procedure Schema Name</param>
        /// <param name="Parameters">Stored Procedure Parameters Dictionary</param>
        /// <param name="sqlConnection">Sql Connection for transaction and execution</param>
        /// <param name="sqlConnectionQuery">Sql Connection for paramter query</param>
        /// <param name="genericList">Result list of generic data</param>
        /// <param name="CloseTransactionConnection">close transaction connection after execution</param>
        /// <param name="CloseQueryConnection">close parameter query connection after execution</param>
        /// <returns>Stored Procedure return value</returns>
        public static int ExecuteGenericListQueryProcedure<T>(string SPSchema, string SPName, ref Dictionary<string, object> Parameters, SqlConnection sqlConnectionTransaction, SqlConnection sqlConnectionQuery, ref List<T> genericList, bool CloseTransactionConnection, bool CloseQueryConnection)
            where T : new()
        {
            int iReturnValue = -100000;//Default to Unclassified error

            SqlCommand sqlCommand = null;
            SqlDataReader sqlDataReader = null;

            try
            {
                sqlCommand = CreateExecuteCommandFromSQLQuery(SPSchema, SPName, sqlConnectionTransaction, ref Parameters, sqlConnectionQuery, CloseQueryConnection);

                OpenSQLConnectionSupressTrx(sqlConnectionTransaction);

                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.SequentialAccess);

                if (genericList != null) genericList = null;
                genericList = new List<T>();

                do
                {
                    ReadDataTable<T>(ref sqlDataReader, ref genericList);
                } while (sqlDataReader.NextResult());

                //All Stored Procedures Must return Value Do not comment this
                if (sqlCommand.Parameters[0].Value == null)
                    throw new Exception("Stored procedure return value cannot be null");

                iReturnValue = (int)sqlCommand.Parameters[0].Value;

                AppendReturnValues(ref sqlCommand, ref Parameters);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseSqlReader(ref sqlDataReader);

                if (sqlCommand != null)
                {
                    sqlCommand.Dispose();
                    sqlCommand = null;
                }

                if (CloseTransactionConnection)
                    CloseSQLConnection(sqlConnectionTransaction, false);
            }
            return iReturnValue;
        }

        /// <summary>
        /// Execute Query Stored Procedure and returns results in DataTable
        /// </summary>
        /// <typeparam name="T">Generic data type</typeparam>
        /// <param name="SPSchema">Stored Procedure Schema Name</param>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="Parameters">Stored Procedure Parameters Dictionary</param>
        /// <param name="sqlConnection">Sql Connection for transaction and execution</param>
        /// <param name="sqlConnectionQuery">Sql Connection for paramter query</param>
        /// <param name="dataTable">Result DataTable</param>
        /// <param name="CloseTransactionConnection">close transaction connection after execution</param>
        /// <returns>Stored Procedure return value</returns>
        public static int ExecuteGenericListQueryProcedure<T>(string SPSchema, string SPName, ref Dictionary<string, object> Parameters, SqlConnection sqlConnectionTransaction, SqlConnection sqlConnectionQuery, ref List<T> genericList, bool CloseTransactionConnection)
            where T : new()
        {
            return ExecuteGenericListQueryProcedure(SPSchema, SPName, ref Parameters, sqlConnectionTransaction, sqlConnectionQuery, ref genericList, CloseTransactionConnection, true);
        }

        /// <summary>
        /// Execute Query Stored Procedure and returns results in DataTable
        /// </summary>
        /// <typeparam name="T">Generic data type</typeparam>
        /// <param name="SPSchema">Stored Procedure Schema Name</param>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="Parameters">Stored Procedure Parameters Dictionary</param>
        /// <param name="sqlConnection">Sql Connection for transaction and execution</param>
        /// <param name="sqlConnectionQuery">Sql Connection for paramter query</param>
        /// <param name="dataTable">Result DataTable</param>
        /// <returns>Stored Procedure return value</returns>
        public static int ExecuteGenericListQueryProcedure<T>(string SPSchema, string SPName, ref Dictionary<string, object> Parameters, SqlConnection sqlConnectionTransaction, SqlConnection sqlConnectionQuery, ref List<T> genericList)
            where T : new()
        {
            return ExecuteGenericListQueryProcedure(SPSchema, SPName, ref Parameters, sqlConnectionTransaction, sqlConnectionQuery, ref genericList, true, true);
        }

        #endregion ExecuteGenericListQueryProcedure - FromSQLQuery

        public static DataTable ExecuteDataTableQuerySQLText(string strSQLText, string strConn)
        {
            try
            {
                //OpenSQLConnectionSupressTrx(sqlConnectionTransaction);
                //CloseSQLConnection(sqlConnectionTransaction, false);

                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(strSQLText, strConn);
                adapter.Fill(dt);
                adapter.Dispose();

                return dt;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        #endregion Public Static Fucntions
    }
}
